package light.head.dao.site;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import light.head.constant.Parm;
import light.head.enm.RESULT;
import light.head.framework.base.BaseDAO;
import light.head.framework.entity.PageBean;
import light.head.framework.utils.DateUtils;
import light.head.model.FileInfo;
import light.head.model.IndexAdvertising;
import light.head.model.IndexAdvertisingAnnex;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Service;

@Service
public class IndexAdvertisingDAO extends BaseDAO<IndexAdvertising, Serializable> {

	private final static String sql_findNew = "FROM IndexAdvertising ia WHERE ia.deleted <> 1 AND ia.deleted IS NOT NULL ORDER BY ia.ct DESC";
	//private final static String sql_loadCount = "SELECT COUNT(*) FROM IndexAdvertising ia WHERE ia.deleted <> 1 AND ia.deleted IS NOT NULL";
	private final static String sql_findAnnexByTmCode = "FROM IndexAdvertisingAnnex iaa WHERE iaa.iac = ?";
	private final static String sql_findIdexAdList = "FROM IndexAdvertising ia WHERE  ia.deleted=? ORDER BY ia.sort";
	public static final String sql_list = "FROM IndexAdvertising ia WHERE ia.deleted=0 ORDER BY ia.sort";
	public static final String sql_list_byCode = "FROM IndexAdvertisingAnnex iaa WHERE iaa.iac = ? AND iaa.cover = 1";

	public RESULT findByMap(Map<String, String> map, PageBean pageBean) {
		StringBuffer hql = new StringBuffer("FROM IndexAdvertising ia WHERE ia.deleted = 0");
		List<Object> list = null;
		if (map != null) {
			list = new ArrayList<Object>();
			String title = map.get(Parm.TITLE);
			String pm = map.get(Parm.PM);
			String sort = map.get(Parm.SORT);
			String time1 = map.get(Parm.TIME1);
			String time2 = map.get(Parm.TIME2);
			if (title != null && title != "") {
				hql.append(" AND ia.title LIKE ?");
				list.add("%" + title + "%");
			}
			if (pm != null && pm != "") {
				hql.append(" AND ia.pm = ? ");
				list.add("%" + pm + "%");
			}
			if (sort != null && sort != "") {
				hql.append(" AND ia.sort = ? ");
				list.add(Integer.parseInt(sort));
			}
			if (time1 != null && time1 != "") {
				list.add(DateUtils.getDateByStr(time1, "yyyy-MM-dd"));
				hql.append(" AND ia.pt >= ?");
			}
			if (time2 != null && time2 != "") {
				list.add(DateUtils.getDateByStr(time2, "yyyy-MM-dd"));
				hql.append(" AND ia.pt <= ?");
			}
		}
		hql.append(" ORDER BY ia.code desc");
		return findToPageBean(hql.toString(), pageBean, list);
	}

	/**
	 * 获取对应的所有附件
	 */
	public List<IndexAdvertisingAnnex> findAnnexByCode(Integer code) {
		List<IndexAdvertisingAnnex> list = find(sql_list_byCode, code);
		return list;
	}

	/**
	 * 删除不在指定编号数组中的附件
	 * 
	 * @param 培训资料编号
	 * @param 排除的附件编号
	 */
	public RESULT deleteAnnexNotInCodes(Integer code, List<Object> codes) {
		StringBuffer sql = new StringBuffer();
		List<Object> parm = new ArrayList<Object>();
		parm.add(code);
		for (Object o : codes) {
			if (o != null) {
				sql.append("?,");
				parm.add(o);
			}
		}
		if (sql.length() > 0) {
			sql.insert(0, " AND iaa.code NOT IN(")
					.deleteCharAt(sql.length() - 1).append(")");
		}
		sql.insert(0, "DELETE FROM IndexAdvertisingAnnex iaa WHERE iaa.iac = ?");
		execute(sql.toString(), parm.toArray());
		return RESULT.SUCCESS;
	}

	/**
	 * 根据拼接好的文件编号序列获取对应的文件列表
	 */
	public List<FileInfo> findFileByCodes(String codes) {
		if (!isNotNull(codes)) {
			return null;
		}
		String sql = "FROM FileInfo f WHERE f.code IN (" + codes + ")";
		List<FileInfo> list = find(sql);
		return list;
	}

	/**
	 * 获取指定数量的最新资料
	 * 
	 * @param 要获取的数量
	 */
	public List<IndexAdvertising> findNew(Integer count) {
		if (null == count)
			return null;
		List<IndexAdvertising> list = find(sql_findNew, 0, count);
		return list;
	}

	/**
	 * 根据资料编号获取相关的附件
	 * 
	 * @param 消息编号
	 */
	public List<IndexAdvertisingAnnex> findAnnexByOccCode(Integer code) {
		List<IndexAdvertisingAnnex> list = find(sql_findAnnexByTmCode, code);
		return list;
	}

	public IndexAdvertising findAdByCode(Integer code) {
		return (IndexAdvertising) super.get(IndexAdvertising.class, code);
	}

	public RESULT fillAnnex(List<IndexAdvertising> list) {
		if (null == list || list.size() == 0)
			return RESULT.PARAMETER_ERROR;

		StringBuffer sql = new StringBuffer(
				"FROM IndexAdvertisingAnnex iaa WHERE iaa.iac IN (");
		List<Object> parm = new ArrayList<Object>();
		for (IndexAdvertising ia : list) {
			sql.append("?,");
			parm.add(ia.getCode());
		}
		sql.deleteCharAt(sql.length() - 1).append(")");
		List<IndexAdvertisingAnnex> alist = find(sql.toString(), null, null,
				parm.toArray());

		if (null != list && list.size() > 0) {
			for (IndexAdvertising ia : list) {
				for (IndexAdvertisingAnnex iaa : alist) {
					if (ia.getCode().equals(iaa.getIac())) {
						if (ia.getList() == null)
							ia.setList(new ArrayList<IndexAdvertisingAnnex>());

						ia.getList().add(iaa);
					}
				}
			}

			return RESULT.SUCCESS;
		}

		return RESULT.NOT_FIND;
	}

	/*public Integer count() {
		if (null == Cache.mtCount)
			loadCount();
		return Cache.mtCount;
	}

	public Integer loadCount() {
		List<BigInteger> list = query(sql_loadCount, null, null);
		if (null != list && list.size() > 0)
			Cache.mtCount = list.get(0).intValue();
		return Cache.mtCount;
	}*/
	
	/**
	 * 加载最新的N条首页广告,及广附件
	 * @param count 加载条数
	 * @return 首页广告List
	 */
	public List<IndexAdvertising> findTopNAds(final Integer count) {
		return this.getHibernateTemplate().execute(new HibernateCallback<List<IndexAdvertising>>() {
			@SuppressWarnings("unchecked")
			@Override
			public List<IndexAdvertising> doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery("From IndexAdvertising ia WHERE ia.deleted = 0 ORDER BY ia.sort");
				query.setFirstResult(0);
				query.setMaxResults(count);
				List<IndexAdvertising> iaList = query.list();
				if (iaList != null && iaList.size() > 0) {
					List<Integer> iaCodes = new ArrayList<Integer>();
					for (IndexAdvertising ia : iaList) {
						iaCodes.add(ia.getCode());
					}
					StringBuffer hqlBuffer = new StringBuffer("FROM IndexAdvertisingAnnex iaa WHERE iaa.cover = 1 AND iaa.iac IN (");
					for (Integer code : iaCodes) {
						hqlBuffer.append(code + ",");
					}
					hqlBuffer.deleteCharAt(hqlBuffer.lastIndexOf(",")).append(")");
					query = session.createQuery(hqlBuffer.toString());
					List<IndexAdvertisingAnnex> iaaList = query.list();
					if (iaaList != null && iaaList.size() > 0) {
						for (IndexAdvertising ia : iaList) {
							Integer iaCode = ia.getCode();
							for (IndexAdvertisingAnnex iaa : iaaList) {
								Integer iaaCode = iaa.getIac();
								if (iaCode.equals(iaaCode)) {
									ia.addList(iaa);
									ia.setCoverPath("indexAdvertisingAnnex/" + iaa.getSn() + "." + iaa.getExt());
								}
							}
						}
					}
					return iaList;
				}
				return null;
			}
		}); 
	}

	public List<IndexAdvertising> getIndexAdList() {
		return super.find(sql_findIdexAdList, false);
	}

	public List<IndexAdvertising> findList() {
		return super.find(sql_list);
	}

	public List<IndexAdvertisingAnnex> findListByCode(Integer code) {
		return super.find(sql_list_byCode, code);
	}
}
